طراحی جدول محصولات و فروش در اکسل به همراه آموزش تابع VLOOKUP

مقدمه
در این مقاله، از پروژه سیستم تحلیل فروش و موجودی محصولات، جدولهای محصولات و فروش را طراحی میکنیم و از تابع VLOOKUP برای ارتباط بین آنها استفاده خواهیم کرد. این تابع به ما کمک میکند اطلاعات مرتبط را از یک جدول دیگر پیدا کنیم. سپس با استفاده از این دادهها، قیمت کل فروش را محاسبه میکنیم. یادگیری VLOOKUP یکی از مهارتهای کلیدی در اکسل است که در بسیاری از پروژهها کاربرد دارد.
مراحل طراحی جدولها و استفاده از VLOOKUP
1. طراحی جدول محصولات
ابتدا جدول محصولات را طراحی میکنیم. این جدول اطلاعات مربوط به محصولات و قیمت آنها را ذخیره میکند.
- یک فایل جدید در اکسل باز کنید.
- ستونهای زیر را در شیت اول (Sheet1) تعریف کنید:
ProductID | ProductName | Category | Price | Stock |
---|---|---|---|---|
101 | لپتاپ | الکترونیک | 25000000 | 50 |
102 | گوشی موبایل | الکترونیک | 15000000 | 100 |
103 | تبلت | الکترونیک | 12000000 | 30 |
104 | هدفون | لوازم جانبی | 500000 | 200 |
- این جدول را با استفاده از گزینه Format as Table در تب Home قالببندی کنید.
- نام جدول را ProductsTable بگذارید:
- برای این کار، جدول را انتخاب کنید.
- به تب Table Design بروید.
- در کادر Table Name، نام
ProductsTable
را وارد کنید.
2. طراحی جدول فروش
حالا جدول فروش را طراحی میکنیم. این جدول اطلاعات مربوط به فروش محصولات را ذخیره میکند.
- در شیت دوم (Sheet2)، ستونهای زیر را تعریف کنید:
SaleID | ProductID | SaleDate | Quantity | UnitPrice | TotalPrice |
---|---|---|---|---|---|
1 | 101 | 2025/02/11 | 2 | ||
2 | 102 | 2025/02/12 | 1 | ||
3 | 104 | 2025/02/12 | 5 |
- این جدول را هم قالببندی کنید و نام آن را SalesTable بگذارید.
3. استفاده از تابع VLOOKUP برای پیدا کردن قیمت محصول
برای پیدا کردن قیمت هر محصول از جدول محصولات، از تابع VLOOKUP استفاده میکنیم. این تابع مقدار موردنظر را در یک جدول جستوجو میکند و داده مرتبط را برمیگرداند.
-
در ستون UnitPrice از جدول فروش (Sheet2)، سلول
E2
را انتخاب کنید. -
فرمول زیر را وارد کنید:
=VLOOKUP([@ProductID], ProductsTable, 4, FALSE)
توضیحات فرمول:
[@ProductID]
: مقدار موجود در ستون ProductID در همان ردیف جدول فروش.ProductsTable
: جدول محصولات که اطلاعات قیمتها در آن ذخیره شده است.4
: شماره ستون در جدول محصولات که مقدار قیمت (Price) را در آن ذخیره کردهایم.FALSE
: جستوجوی دقیق (Exact Match) را انجام میدهد.
-
این فرمول، قیمت واحد محصول را از جدول محصولات پیدا میکند و در ستون UnitPrice نمایش میدهد.
-
فرمول را به سایر ردیفها کپی کنید تا قیمت واحد برای همه محصولات محاسبه شود.
4. محاسبه قیمت کل
حالا با استفاده از قیمت واحد و تعداد فروش، قیمت کل را محاسبه میکنیم.
-
در ستون TotalPrice از جدول فروش، سلول
F2
را انتخاب کنید. -
فرمول زیر را وارد کنید:
=[@UnitPrice] * [@Quantity] -
این فرمول، قیمت واحد را در تعداد فروش ضرب میکند و قیمت کل را محاسبه میکند.
-
فرمول را به سایر ردیفها کپی کنید.
نکات مهم در استفاده از VLOOKUP
- ترتیب ستونها: ستون مورد جستوجو (ProductID) باید همیشه اولین ستون در جدول جستوجو (ProductsTable) باشد.
- استفاده از FALSE: همیشه از گزینه
FALSE
(Exact Match) برای جلوگیری از خطاهای جستوجو استفاده کنید. - نامگذاری جدولها: نامگذاری جدولها باعث خوانایی بهتر فرمولها و کاهش خطا میشود